﻿<%@ WebHandler Language="C#" Class="ZSOilInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 装配试车厂油封统计报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.24
 **/

public class ZSOilInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT n.*
FROM 
(
    SELECT t.oilType,
            CASE WHEN m.yfCount > 1 AND t.createdate < m.maxCreatadate THEN '已再次油封'
                ELSE t.status END status,
            t.engineno,t.createdate,t.startdate,t.enddate,
            t.locationname,t.oilsealcycle,t.fullname,t.qty,t.productname,t.description,
            t.productbatchno,t.isjz,t.type,t.notes,t.releasesealdate
    FROM
    (
            SELECT el.createdate,el.partid,el.productid,
                CASE WHEN el.containerno IS NULL AND el.partid IS NULL THEN '整机油封'
                        WHEN el.containerno IS NOT NULL AND el.productid IS NULL THEN '部件油封'
                        WHEN el.productid IS NOT NULL AND el.containerno IS NOT NULL THEN '组件油封' END oilType,
                CASE WHEN el.releasesealdate IS NOT NULL THEN '解封'
                        WHEN el.releasesealdate IS NULL AND SYSDATE > el.enddate THEN '超期'
                        WHEN el.releasesealdate IS NULL AND SYSDATE > el.reminderdate THEN '预警期'
                        ELSE '油封期中' END status,
                el.engineno,el.startdate,el.enddate,lc.locationname,os.oilsealcycle,e.fullname,el.qty,
                NVL(pb.productname,pb2.productname) productname,NVL(p.description,p2.description) description,
                el.productbatchno,el.isjz,el.type,el.notes,el.releasesealdate
            FROM enginepartoilseal el
            LEFT JOIN LOCATION lc ON lc.locationid = el.locationid
            LEFT JOIN oilsealcycleinfo os ON os.oilsealcycleinfoid = el.oilsealcycleinfoid
            LEFT JOIN employee e ON e.employeeid = el.employeeid
            LEFT JOIN product p ON p.productid = el.productid --零件件号
            LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
            LEFT JOIN product p2 ON p2.productid = el.partid --部件件号
            LEFT JOIN productbase pb2 ON pb2.productbaseid = p2.productbaseid
            WHERE el.isuse = 0 ";
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND el.engineno LIKE '%{0}%' ", jObject["engineno"].ToString());
        }

        //油封开始时间
        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND el.startdate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND el.startdate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //保存地点
        if (jObject["locationname"] != null && jObject["locationname"].ToString() != "")
        {
            sql += string.Format(" AND lc.locationname LIKE '%{0}%' ", jObject["locationname"].ToString());
        }

        sql += @"
                    ) t 
                        LEFT JOIN 
                        (
                             SELECT el.engineno,el.productid,el.partid,COUNT(1) yfCount,MAX(el.createdate) maxCreatadate
                             FROM enginepartoilseal el
                             GROUP BY el.engineno,el.productid,el.partid
                        ) m ON nvl(m.productid,1) = nvl(t.productid,1) AND m.engineno = t.engineno AND nvl(m.partid,2) = nvl(t.partid,2)
                    ) n WHERE 1=1 ";

        //油封类型
        if (jObject["oiltype"] != null && jObject["oiltype"].ToString() != "")
        {
            sql += string.Format(" AND n.oilType = '{0}' ", jObject["oiltype"].ToString());
        }

        //油封状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            sql += string.Format(" AND n.status = '{0}' ", jObject["status"].ToString());
        }

        sql += " ORDER BY n.createdate DESC ";
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["OILTYPE"].Caption = "油封类型";
        dt.Columns["STATUS"].Caption = "油封状态";
        dt.Columns["ENGINENO"].Caption = "发动机号";
        dt.Columns["CREATEDATE"].Caption = "油封创建时间";
        dt.Columns["STARTDATE"].Caption = "油封开始时间";
        dt.Columns["ENDDATE"].Caption = "油封结束时间";
        dt.Columns["LOCATIONNAME"].Caption = "保存地点";
        dt.Columns["OILSEALCYCLE"].Caption = "油封期";
        dt.Columns["FULLNAME"].Caption = "操作工";
        dt.Columns["QTY"].Caption = "油封数量";
        dt.Columns["PRODUCTNAME"].Caption = "件号";
        dt.Columns["DESCRIPTION"].Caption = "名称";
        dt.Columns["PRODUCTBATCHNO"].Caption = "批次";
        dt.Columns["ISJZ"].Caption = "假装";
        dt.Columns["TYPE"].Caption = "类型";
        dt.Columns["NOTES"].Caption = "备注";
        dt.Columns["RELEASESEALDATE"].Caption = "解封日期";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "油封统计报表");
    }
}